Нужно выделить все формулы?

Нажимаем Ctrl+G

В открывшемся окне "Переход" нажимаем "Выделить" (Special)

Далее — "Формулы" (Formulas)

Готово. Можно теперь покрасить ячейки с формулами, если хочется.

Хотите, чтобы в фигуре отображался какой-нибудь текст, сформированный формулой?

Например, текущее время с какой-нибудь надписью (Текущее время: 12:00") или что-то другое ("Выручка на дату 01.06: 1,2 млн")?

Для этого формируем в ячейке текст формулой, а потом ссылаемся на ячейку с формулой из фигуры (выделяйте фигуру, вводите знак "равно" и кликайте по ячейке, как в обычной формуле).

В примере используется функция ТДАТА / NOW — это текущие дата и время. И функция ТЕКСТ / TEXT — напоминаем, при объединении в текст числовых (а дата и время = число) значений они теряют форматирование. Если вам нужно время в заданном формате, например, ЧЧ:ММ, используйте функцию ТЕКСТ, которая превращает число в текст, но в нужном формате.

Суммируем с условием только видимые строки

Просто суммировать (а также считать среднее и еще несколько базовых операций) скрытые строки — это функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
А сумма с условием — это SUMIFS / СУММЕСЛИМН. Эта функция считает не скрытые, а все строки.

Как совместить и считать по видимым строкам с условием?

Во всех версиях — со вспомогательным столбцом, в Microsoft 365 можно и формулой, оба варианта здесь:
https://teletype.in/@renat_shagabutdinov/subtotal_sumifs

Выделяем цветом формулы по какому-то признаку

Вы хотите выделить визуально "старые формулы массива" (из версий до 2019 включительно), или формулы, ссылающиеся на какой-то лист, или формулы с определенными функциями.

Получить текст формулы можно с помощью функции Ф.ТЕКСТ / FORMULATEXT. Искать в этом тексте какой-то признак можно с помощью функции НАЙТИ / FIND.
И если все это засунуть в условное форматирование, то мы получим возможность выделять визуально формулы, содержащие что-нибудь!

Например, старые формулы массива можно выделить по наличию фигурной скобки:

=НАЙТИ("{";Ф.ТЕКСТ(первая ячейка форматируемого диапазона))

Ссылки на лист с названием — по этому самому названию

=НАЙТИ("название листа";Ф.ТЕКСТ(первая ячейка ...))

Определенные функции — по их названию. Например, ПРОСМОТРX, которой нет в старых версиях:

=НАЙТИ("ПРОСМОТРX";Ф.ТЕКСТ(ячейка))

А вот выделить формулы со старой функцией ПРОСМОТР можно, добавив к "запросу" скобку — иначе будут выделяться формулы, где есть и ПРОСМОТР, и ПРОСМОТРX.
=НАЙТИ("ПРОСМОТР(";Ф.ТЕКСТ(ячейка))

Мгновенное заполнение (Flash Fill)

— когда вы нажимаете Ctrl+E (или включаете мгновенное заполнение с ленты инструментов, Главная — Заполнить), Excel анализирует всю строку, а не один столбец. Так что можно собирать данные из нескольких столбцов
— Можно исправлять регистр текста
— Можно добавлять символы, например, точки

Все это — в примере с ФИО, где мы добавляем точки, делая инициалы вместо полных ИО, собираем данные из трех столбцов, исправляем регистр. Все это без формул и быстро :) Но только с версии Excel 2013 включительно.

Часто создаете "умные" таблицы?

Хорошая практика — их переименовывать (чтобы в формулах ссылаться не на "Таблица1", "Таблица2", а на "Прайс" или "Остатки")

Если приходится переименовывать их часто, поле "Имя таблицы" можно добавить на панель быстрого доступа! И оно всегда будет наверху во всех книгах Excel при любой активной вкладке ленты инструментов.

Активно оно будет, конечно, только когда вы будете трогать руками таблицы. При активации обычных диапазонов поле будет серым, но с панели быстрого доступа никуда не уйдет.

Чтобы добавить инструмент на панель, просто щелкните по нему правой кнопкой мыши и выберите соответствующую команду в контекстном меню.

Окно «Найти и заменить» (Find and Replace) во многих случаях помогает решить задачи по обработке текстовых значений (и не только) без применения сложных функций и формул. Это окно позволяет исправить большое количество формул, поменять форматирование всех однотипных ячеек, удалить определенные слова или символы из диапазона или из всей книги Excel.

Его можно вызвать сочетаниями клавиш Ctrl + F (⌘ + F) или Ctrl + H (⌃ + H) — в обоих случаях откроется одно и то же диалоговое окно, но в первом случае на вкладке «Найти» (Find), а во втором — «Заменить» (Replace).

Вот несколько нюансов:
— Если вы предварительно выделили диапазон ячеек, то поиск/замена будут производиться в пределах этого диапазона. Если же нет — то на листе или в книге (изменить этот параметр можно в поле «Искать» (Within) в окне «Найти и заменить»; по умолчанию будет лист).

— Если вы хотите что-то удалять, а не заменять, просто оставьте поле «Заменить на» пустым. Заменить на ничто = удалить, не так ли?

— Можно производить изменения сразу с большим количеством формул. Например, вам нужно поменять диапазон или функцию во многих формулах. Выделите диапазон с формулами, вызовите окно «Найти и заменить» и введите в поле «Найти» тот фрагмент формул, который вы хотите изменить, а в «Заменить на» — то, на что хотите его изменить. Убедитесь, что в списке «Область поиска» (Look in) заданы «Формулы» (Formulas).

Ctrl + левая кнопка мыши: быстрое копирование листов или объектов

Нужно создать копию листа? Зажимаем Ctrl и тянем ярлык существующего листа мышкой. Получаем копию.

Это чудо работает не только с листами, но и с фигурами, например (см видео). Или с диаграммами.

И не только в Excel, но и в других приложениях. Например, в Power Point или Google Презентациях 🔥

Быстрая фильтрация в сводной таблице

Если вам нужно быстро исключить некоторые значения из сводной: выделите то, что нужно убрать (в строках или столбцах отчета сводной таблицы) и нажмите Ctrl + - (минус).

Данные будут отфильтрованы, те значения, что вы выделяли, будут исключены в фильтре.

Функция SCAN: нарастающий итог — простой, по каждому году/месяцу или с условием

SCAN — одна из вспомогательных функций LAMBDA, которая позволяет пробегаться по массиву, обращаясь к каждому элементу и накопленному итогу. И творить всякую магию. Доступно это удовольствие в Google Таблицах и в Excel 365 / Excel Online.

В этой статье разбираем ее синтаксис и разные варианты расчета нарастающего итога:
— простой нарастающий итог — для демонстрации работы функции
— нарастающий итог в рамках каждого месяца(периода). То есть одной формулой для всей таблицы получаем нарастающий итог в рамках месяца (или года/недели/другого периода), а с началом периода он обнуляется и начинается по новой.
— нарастающий итог по условию. То есть считаем только определенные строки, а не все (например, выручку только в те дни, когда работал определенный администратор). Строки, в которых условие не выполняется, в нарастающий итог не попадают.

Файлы с примерами из статьи:
Рабочая книга Excel
Google Таблица

https://teletype.in/@renat_shagabutdinov/scanexcelsheets

Если дата в ячейках записана как текстовое значение вида ДДММГГГГ, без точек/дефисов/других разделителей, можно превратить такой текст в настоящую дату формулой:

=ДАТА( ПРАВСИМВ( ячейка с датой; 4) ; ПСТР (ячейка; 3; 2) ; ЛЕВСИМВ (ячейка; 2) )

Функция ДАТА / DATE возвращает дату, заданную тремя параметрами — годом, месяцем и днем.
Ее аргументы мы получаем текстовыми функциями:
Год — извлекая первые цифры цифры с помощью ПРАВСИМВ / RIGHT (она возвращает первые N символов из текстовой строки)
Месяц — извлекая два символа, начиная с третьего, с помощью ПСТР / MID.
День — последние две цифры с помощью функции ЛЕВСИМВ / RIGHT.

Вот так новости!
Наконец-то в Excel появились функции для работы с регулярными выражениями. Но, увы, как водится с новинками — только у подписчиков 365.

Напоминаем, что в Google Таблицах функции для работы с регулярками есть и доступны всем — там они называются REGEXEXTRACT (извлекаем), REGEXMATCH (проверяем соответствие), REGEXREPLACE (заменяем).
В Excel до появления этих функций можно было работать с регулярками через макросы — вот статья маэстро Николая Павлова на эту тему.

Официальная новость про функции

Вот некоторые материалы по регулярным выражениям из нашего канала про Google Таблицы (больше найдете в канале по поиску, примеров очень много):
Вытаскиваем utm из ссылки

Приводим mm-dd к dd-mm не формулой

Меняем формат даты с ММ/ДД/ГГГГ на ДД.ММ.ГГГГ формулой

Таблица с примерами регулярок от участников сообщества

Извлекаем числа, едим пончики

Извлекаем актуальное число подписчиков телеграм-каналов из ссылки вида www.tg-me.com/канал

Получаем последнюю дату из таблицы

Если нам просто нужно последнее значение из столбца (по порядку, нижнее) - можно использовать функцию ПРОСМОТР / LOOKUP. Введем у нее в первом аргументе число, которое априори больше любой даты (можно просто 100 000). И тогда ПРОСМОТР вернет последнее значение.
(подробнее про ПРОСМОТР читайте здесь)

Если нужна самая поздняя дата, то можно вспомнить, что любая дата в Excel — это число, и просто взять максимальное число из столбца с помощью функции МАКС / MAX. Это будет последняя дата, в какой бы строке она ни находилась.

А если нужна не просто самая поздняя, а поздняя у определенного администратора? С условием, иначе говоря.
Если у вас Excel 2019, 2021, Google Таблицы — можно воспользоваться функцией МАКСЕСЛИ / MAXIFS (подробнее о ней тут; она работает как СУММЕСЛИМН / SUMIFS, только не суммирует, а ищет максимум).
А если другие версии Excel?
Сделаем МАКСЕСЛИ сами. Из... МАКС и ЕСЛИ :)

Функцией ЕСЛИ будем проверять столбец с именами на соответствие нужному (B2:B132="Лемур") и возвращать при выполнении условия даты из столбца A. При невыполнении условия функция ЕСЛИ вернет просто логическое значение ЛОЖЬ, так как мы ничего явно в третьем аргументе не указали. На выходе получим массив, где будут даты, когда работал Лемур, и значения ЛОЖЬ, когда работали другие.
Функция МАКС в этом массиве найдет самую большую дату.
Чтобы формула сработала, ее нужно ввести как формулу массива — заклинанием Ctrl+Shift+Enter.

Видеоурок: модель данных Power Pivot. Создание отношений между таблицами.

Друзья, делюсь с вами одним из уроков нового модуля курса "Магия Excel", посвященного модели данных Excel (Power Pivot).
https://www.youtube.com/watch?v=IR-rjAsC968

А весь курс можно найти тут — в нем 14 модулей и сотни минут таких видеоуроков, домашки и файлы со всеми примерами — в исходном и готовом виде:
https://www.mann-ivanov-ferber.ru/courses/magicexcel/

В строке формул можно переходить на следующую строку с помощью Alt+Enter. Это позволяет визуально разделить отдельные фрагменты/функции — тогда формулу будет проще воспринимать (вашим коллегам и вам самим в будущем, когда вы уже забудете ее логику).

Это может помочь, если у вас уже многоэтажная формула, а в ней возникает синтаксическая ошибка. Обратите внимание, что высоту строки формул можно менять — достаточно потянуть за нижнюю границу, удерживая нажатой левую кнопку мыши.

Также можно пробелами ставить отступы в формуле, если это поможет вам с восприятием формулы

На скриншоте формула с переносами строк, но без отступов. Каждая функция начинается с новой строки. На работу формулы это не влияет.

Закрепление верхней строки в Excel в один клик: добавляем команды на панель быстрого доступа

Панель быстрого доступа (Quick Access Toolbar, QAT) — простейший инструмент для настройки интерфейса "под себя". Туда можно добавить любую команду — как с ленты, так и из списка вообще всех команд и инструментов Excel.

Даже если какое-то действие нельзя добавить напрямую из ленты (потому что оно там находится в выпадающем списке, в коллекции — как, например, закрепление верхней строки находится в коллекции "Закрепление областей"; или потому что действия вообще нет на ленте) — его все равно можно добавить через параметры Excel, чтобы во всех книгах у вас всегда был доступ к нужной команде в один клик. В мини-статье разбираем, как это сделать — как раз на примере закрепления верхней строки.

В связи с недавним выходом новинки, посвященной языку M (он используется в Power Query в Excel и Power BI) время обновить обзор основных книг по каждой эксельной теме 😸

Итак, если вы хотите изучить конкретную тему в рамках Excel, вот по одной книге на каждую.

Excel в целом
Microsoft Excel Inside Out (Office 2021 and Microsoft 365)
На русском:
Excel 2019. Библия пользователя — Куслейка, Александер

Макросы
Microsoft Excel VBA and Macros — Bill Jelen
На русском: Excel 2016. Профессиональное программирование на VBA — Александер, Куслейка (не пугайтесь версии 2016 — макросы не меняются десятилетиями)

Сводные таблицы
Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 — Джелен

Power Query
Скульптор данных в Excel с Power Query — Николай Павлов
или / и
Приручи данные с помощью Power Query в Excel и Power Bi — Пульс, Эскобар

Язык M
The Definitive Guide to Power Query (M): Mastering complex data transformation with Power Query
(книга для глубокого погружения именно в язык M, то есть ее лучше читать с опытом работы в интерфейсе Power Query и при желании решать там нестандартные задачи и писать код самостоятельно)

Power Pivot и язык формул DAX (который используется и в Power BI / других решениях Microsoft)
Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel — Руссо, Феррари

Очень глубоко и основательно про DAX: Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel — Руссо, Феррари

Для первого ознакомления с Power Pivot можно начать с глав в книге Джелена про сводные

Формулы в целом
С новыми формулами (LAMBDA, новые массивы), от начального до продвинутого уровня: главы про формулы в Microsoft Excel Inside Out.
С новыми формулами посложнее: Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas
На русском с новыми формулами: главы про формулы у меня в "Магии таблиц"
На русском до 2019 включительно от начального до продвинутого: главы про формулы в Excel 2019. Библия пользователя
На русском до 2019 включительно посложнее: Мастер формул — Николай Павлов

Старые формулы массива (до 2019 включительно)
Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic — Girvin
На русском: Мастер формул — Николай Павлов

Новые формулы массива (динамические массивы)
Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond
На русском: немного есть у меня в "Магии таблиц"

Визуализация
Визуализация данных при помощи дашбордов и отчетов в Excel — Куслейка

Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста

Весной 2024 года в Excel были анонсированы функции для работы с регулярными выражениями. Ранее они были доступны в Google Spreadsheets. Разбираем несколько примеров. Извлекаем:
- вес из длинного названия товара
- все адреса электропочты из ячейки с текстом
- даты в разных форматах
- текст в скобках.

https://www.youtube.com/watch?v=xylfF5lS3WY

Для всех, кто устал от некачественной конвертации и бесконечного поиска редакторов – встречайте ContentReader PDF. Расправься с бумажной волокитой навсегда, скачав всего одну программу.

• Оцифровывай на максималках
Распознавай 198 языков, включая таблицы, формулы и графики.

• Стань гуру конвертации
Конвертируй 20 форматов туда-сюда без сливания файлов в онлайн.

• Повелевай доступом
Запароль курсовую или договор от копирования и печати.

• Редактируй без боли
Вноси правки в PDF без конвертации и тревожности – абзац никуда не уедет, структура документа не нарушится.

• Комментируй на здоровье
Пиши горячие комментарии всем соавторам документа.

Сомневаешься? Попробуй сам — по ссылке пробная версия на 7 дней.

А для тех, кто уже определился и подписался на Telegram-канал Content AI, дарим 10% скидку на покупку редактора по промокоду TRIALDISCOUNT10.

Успейте купить по ссылке, промокод действителен еще 3 дня.

Реклама ООО "Контент ИИ" ИНН 9715416652